/*NOTE: This is model code to illustrate the logic used to generate the datasets for analysis in the SCARP pilot.
This code is not intended to run as is - due to Epic restrictions on the sharing of intellectual property all 
of the table and attribute names have been "masked" with names enclosed in square brackets if they were pulled 
from Clarity (EPIC).*/

/*****************************************************
PROJECT:  BRIDGE C2 
PILOT: SCRAP
DATE: 7/22/2022

DESCRIPTION: 
Use ASCEND approach to compare CRC, CVC and 
BC cancer prevention services in patients with
	No SDH Screening
	Documented Need
	Documented No Need
Stratified by SDH need: Food, Housing, Transportation
STUDY PERIOD: 7/1/2016-2/29/2020		

Note: SDH screens for this project were pulled and sent to OHSU for ASCEND. 
This project uses that pull for the SDH variables as the study date range has not changed.

DETAILS (This Syntax): 
Pull raw_patids for patients in ASCEND data pull, link to scrap encounters file and patient file

INPUT DATA: 
ASCEND.AIM1_8_SDH_screening_2020 
ASCEND.AIM1_8_ENCOUNTER_2020
ASCEND.AIM1_8_PATIENT_2020
OCHIN.demographic
OCHIN.ENCOUNTER


OUTPUT DATA:
SCRAP.SCRAP_ASCEND_patid_xwalk
SCRAP.SCRAP_encounters
SCRAP.SCRAP_SDH_screening

SECTIONS:
A. Link ASCEND encounters to sex_r,
	Get raw_patids for all potential SCRAP patients in ASCEND encounters
		Males 50-74
		Females 23-74 
B. Determine current ADVANCE_patids
C. Create crosswalk of raw_PATid, ADVANCE_patid to ASCEND_patid
		>>SCRAP.SCRAP_ASCEND_patid_xwalk
D. link raw_patid and ADVANCE_patid to ASCEND SDH_screening, Encounters
		>>SCRAP.SCRAP_encounters
		>>SCRAP.SCRAP_SDH_screening

*****************************************************/
options compress=yes reuse=yes;
libname SCRAP "e:\\sasroot\BRIDGE C2\SCRAP\";

/*A. Link ASCEND encounters to sex_r,*/

/*	Get raw_patids for all potential SCRAP patients in ASCEND encounters*/
/*		Males 50-74*/
/*		Females 23-74 */

proc sql;
create table encounter as
select 
e.*,
p.sex_r
from ASCEND.AIM1_8_ENCOUNTER_2020 e
left join ASCEND.aim1_8_patient_2020 p
on e.patid=p.patid;

/*Limit to potential scrap encounters*/
data encounter;
set encounter;
rename patid=ASCEND_patid;
if age_at_encounter_date>=50 then do;
	if age_at_encounter_date<75 then output;
end;
else if age_at_encounter_date<50 then do;
	if sex_r='F' then do;
		if age_at_encounter_date>=23 then output;
	end;
end;
run;


/*
B. Determine current ADVANCE_patids
C. Create crosswalk of raw_PATid, ADVANCE_patid to ASCEND_patid

These two sections are internalto create a crosswalk of raw_PATid, ADVANCE_patid to ASCEND_patid: 
	output file:SCRAP.SCRAP_ASCEND_patid_xwalk
*/


/*D. link raw_patid and ADVANCE_patid to ASCEND SDH_screening, Encounters*/
/*		>>SCRAP.SCRAP_encounters*/
/*		>>SCRAP.SCRAP_SDH_screening*/

proc sql;
create table SCRAP.SCRAP_encounters as
select x.ADVANCE_patid,
x.raw_patid,
e.*
from SCRAP.SCRAP_ASCEND_patid_xwalk x left join encounter e
on x.ascend_patid=e.ascend_patid;


proc sql;
create table SCRAP.SCRAP_SDH_screening as
select x.ADVANCE_patid,
x.raw_patid,
e.*
from SCRAP.SCRAP_ASCEND_patid_xwalk x left join ascend.aim1_8_sdh_screening_2020 e
on x.ascend_patid=e.patid;

